import pymysql.cursors

# Connect to the database
connection = pymysql.connect(host='localhost',
                             user='root',
                             password='chenhui',
                             db='springlearn',
                             charset='utf8mb4',
                             cursorclass=pymysql.cursors.DictCursor)

try:
    # with connection.cursor() as cursor:
    #     # Create a new record
    #     sql = "INSERT INTO `user` (`username`, `password`) VALUES (%s, %s)"
    #     effect_count = cursor.execute(sql, ('huhx', '123456'))
    #     print('effect counts = %d.' % effect_count)
    #
    # # connection is not autocommit by default. So you must commit to save your changes.
    # connection.commit()
    #
    # with connection.cursor() as cursor:
    #     # Read a single record
    #     sql = "SELECT `user_id`, `password` FROM `user` WHERE `username`=%s"
    #     cursor.execute(sql, ('huhx',))
    #     result = cursor.fetchone()
    #     print(result)

        # fetch all
    with connection.cursor() as cursor:
        # sql = "select * from USER"
        # cursor.execute(sql)
        # print(cursor.fetchone())
        # print(cursor.fetchone())

        # for item in cursor.fetchmany(2):
        #     print(item)

        # for item in cursor.fetchall():
        #     print(item)

        # sql = "insert into user(username, password) VALUES (%(username)s, %(password)s)"
        # dict1 = {'username': 'liu', 'password': 'ling'}
        # dict2 = {'username': 'chen', 'password': 'hui'}
        # data_list = [dict1, dict2]

        sql = "insert into user(username, password) VALUES (%s, %s)"
        data1 = ('chen', 'hui')
        data2 = ('liu', 'ling')
        data_list = [data1, data2]
        cursor.executemany(sql, data_list)
        connection.commit()

finally:
    connection.close()

# effect counts = 1.
# {'user_id': 1, 'password': '123456'}